﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsMatInStockDataAccess
    {
        public static DataTable SelectMat(string PurchaseID, string strFrom, string strTo, string WorkOrderNo, string MaterialName, string SupplierID, string MatInstockID, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"	SELECT distinct bp.BillID as PurchaseID,bp.SupplierID,bp.BillDate,
	bpd.BillDetailID as PurchaseDetailID,bpd.MaterialID,bpd.WorkOrderNo,
	bpd.Nums as PurchaseNum,bpd.Price,a.ReceiveNums,mm.MaterialName,mm.Specifications
	,mm.Unit,mm.MeterWeight,mm.MMNumber as SingleMMNumber,ISNULL(bpd.Nums,0)-ISNULL(a.ReceiveNums,0) AS LeftNums
	FROM BS_Purchase bp
	LEFT JOIN BS_PurchaseDetail bpd ON bpd.BillID=bp.BillID
	LEFT JOIN (SELECT bmid.PurchaseDetailID,SUM(ISNULL(bmid.Nums,0)-ISNULL(bmid.UnqualifyNum,0)) ReceiveNums
			   FROM BS_MatInstock bmi
				LEFT JOIN BS_MatInstockDetail bmid ON bmid.BillID=bmi.BillID
			   WHERE bmi.[Status]<>'0' AND bmi.BillID<>@strMatInstockID
			   GROUP BY bmid.PurchaseDetailID) a ON a.PurchaseDetailID=bpd.BillDetailID
	LEFT JOIN MD_Material mm ON mm.MaterialID=bpd.MaterialID
	WHERE bp.[Status]<>'0' AND ISNULL(bpd.Nums,0)-ISNULL(a.ReceiveNums,0)>0 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                strcmd += " and bp.SupplierID = @strSupplierID";
                dpc.AddWithValue("@strSupplierID", SupplierID);
                dpc.AddWithValue("@strMatInstockID", MatInstockID);
                if (!string.IsNullOrEmpty(PurchaseID))
                {
                    strcmd += " and bp.PurchaseID LIKE @strPurchaseID";
                    dpc.AddWithValue("@strPurchaseID", "%" + PurchaseID + "%");
                }
                if (!string.IsNullOrEmpty(WorkOrderNo))
                {
                    strcmd += " and bpd.WorkOrderNo LIKE @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + WorkOrderNo + "%");
                }
                if (!string.IsNullOrEmpty(MaterialName))
                {
                    strcmd += " and mm.MaterialName LIKE @strMaterialName";
                    dpc.AddWithValue("@strMaterialName", "%" + MaterialName + "%");
                }
                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and bp.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(MaterialName))
                {
                    strcmd += " and bp.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                strcmd += " ORDER BY bp.BillDate DESC";

                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable GetMatInStockDataByID(string id)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select mis.*,s.SupplierName,misd.BillDetailID,misd.PurchaseID,misd.PurchaseDetailID,
		                                misd.WorkOrderNo,misd.MaterialID,misd.Price,misd.Nums,misd.Amount,misd.MMNumber,
		                                m.MaterialName,m.Specifications,m.Unit,m.MeterWeight,pd.Nums as PurchaseNum,
		                                ISNULL(pd.Nums,0)-ISNULL(c.RecNums,0) as LeftNums,m.MMNumber as SingleMMNumber
		
                                from BS_MatInStock mis
                                left join BS_MatInStockDetail misd on misd.BillID=mis.BillID
                                left join BS_PurchaseDetail pd on pd.BillDetailID=misd.PurchaseDetailID and pd.BillID=misd.PurchaseID
                                left join MD_Material m on m.MaterialID=misd.MaterialID
                                left join MD_Supplier s on s.SupplierID=mis.SupplierID
                                left join (select PurchaseDetailID,MaterialID,SUM(ISNULL(Nums,0)) as RecNums 
                                                from BS_MatInStockDetail
                                                where BillID<>@BillID
                                                group by PurchaseDetailID,MaterialID) c on c.MaterialID=pd.MaterialID and c.PurchaseDetailID=misd.PurchaseDetailID
                                where mis.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@BillID", id);

                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IMatInStockCount, out int icurPageIndex, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_MatInStockDetail A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, PageSize, dpc, IMatInStockCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
